#!/usr/bin/env node

/**
 * Sequelize数据库初始化脚本
 * 使用Sequelize + MySQL2连接数据库并创建表结构
 */

const { Sequelize, DataTypes } = require('sequelize');

// 数据库连接配置（请根据实际情况修改）
const DB_CONFIG = {
  host: 'localhost',
  port: 3306,
  user: 'root',
  password: '12345678', // 请修改为实际密码
  database: 'sso_system',
  dialect: 'mysql',
  dialectModule: require('mysql2'),
};

// 初始化Sequelize
const sequelize = new Sequelize({
  database: DB_CONFIG.database,
  username: DB_CONFIG.user,
  password: DB_CONFIG.password,
  host: DB_CONFIG.host,
  port: DB_CONFIG.port,
  dialect: DB_CONFIG.dialect,
  dialectModule: DB_CONFIG.dialectModule,

  // 连接池配置
  pool: {
    max: 5,
    min: 0,
    acquire: 30000,
    idle: 10000,
  },

  // 字符集配置
  dialectOptions: {
    charset: 'utf8mb4',
    collate: 'utf8mb4_unicode_ci',
  },

  // 日志配置
  logging: console.log,

  // 时区配置
  timezone: '+08:00',

  // 其他配置
  define: {
    timestamps: true,
    underscored: true,
    freezeTableName: true,
    charset: 'utf8mb4',
    collate: 'utf8mb4_unicode_ci',
  },
});

// 定义用户表模型
const User = sequelize.define('User', {
  id: {
    type: DataTypes.BIGINT,
    primaryKey: true,
    autoIncrement: true,
    comment: '用户ID，主键自增',
  },
  username: {
    type: DataTypes.STRING(50),
    allowNull: false,
    unique: true,
    comment: '用户名，唯一索引',
  },
  password_hash: {
    type: DataTypes.STRING(255),
    allowNull: false,
    comment: '密码哈希值，使用bcrypt加密',
  },
  salt: {
    type: DataTypes.STRING(32),
    allowNull: false,
    comment: '密码盐值，增强安全性',
  },
  status: {
    type: DataTypes.TINYINT,
    defaultValue: 1,
    comment: '用户状态：1-正常，0-禁用，2-待激活',
  },
  last_login_at: {
    type: DataTypes.DATE,
    allowNull: true,
    comment: '最后登录时间',
  },
  last_login_ip: {
    type: DataTypes.STRING(45),
    allowNull: true,
    comment: '最后登录IP地址，支持IPv6',
  },
  login_count: {
    type: DataTypes.INTEGER,
    defaultValue: 0,
    comment: '登录次数统计',
  },
}, {
  tableName: 'users',
  comment: '用户基础信息表',
  indexes: [
    { fields: ['username'], name: 'idx_username' },
    { fields: ['status'], name: 'idx_status' },
    { fields: ['created_at'], name: 'idx_created_at' },
  ],
});

// 定义用户会话表模型
const UserSession = sequelize.define('UserSession', {
  id: {
    type: DataTypes.BIGINT,
    primaryKey: true,
    autoIncrement: true,
    comment: '会话ID，主键自增',
  },
  session_id: {
    type: DataTypes.STRING(128),
    allowNull: false,
    unique: true,
    comment: '会话标识符，唯一索引',
  },
  user_id: {
    type: DataTypes.BIGINT,
    allowNull: false,
    comment: '关联用户ID',
    references: {
      model: User,
      key: 'id',
    },
    onDelete: 'CASCADE',
  },
  session_data: {
    type: DataTypes.TEXT,
    allowNull: true,
    comment: '会话数据，JSON格式存储',
  },
  ip_address: {
    type: DataTypes.STRING(45),
    allowNull: false,
    comment: '登录IP地址',
  },
  user_agent: {
    type: DataTypes.TEXT,
    allowNull: true,
    comment: '用户代理信息（浏览器、设备等）',
  },
  expires_at: {
    type: DataTypes.DATE,
    allowNull: false,
    comment: '会话过期时间',
  },
  is_active: {
    type: DataTypes.TINYINT,
    defaultValue: 1,
    comment: '会话状态：1-活跃，0-已失效',
  },
}, {
  tableName: 'user_sessions',
  comment: '用户会话管理表',
  indexes: [
    { fields: ['session_id'], name: 'idx_session_id' },
    { fields: ['user_id'], name: 'idx_user_id' },
    { fields: ['expires_at'], name: 'idx_expires_at' },
    { fields: ['is_active'], name: 'idx_is_active' },
  ],
});

// 定义登录日志表模型
const LoginLog = sequelize.define('LoginLog', {
  id: {
    type: DataTypes.BIGINT,
    primaryKey: true,
    autoIncrement: true,
    comment: '日志ID，主键自增',
  },
  user_id: {
    type: DataTypes.BIGINT,
    allowNull: true,
    comment: '用户ID，登录成功时记录',
    references: {
      model: User,
      key: 'id',
    },
    onDelete: 'SET NULL',
  },
  username: {
    type: DataTypes.STRING(50),
    allowNull: false,
    comment: '尝试登录的用户名',
  },
  ip_address: {
    type: DataTypes.STRING(45),
    allowNull: false,
    comment: '登录IP地址',
  },
  user_agent: {
    type: DataTypes.TEXT,
    allowNull: true,
    comment: '用户代理信息',
  },
  login_result: {
    type: DataTypes.TINYINT,
    allowNull: false,
    comment: '登录结果：1-成功，0-失败',
  },
  failure_reason: {
    type: DataTypes.STRING(100),
    allowNull: true,
    comment: '失败原因：用户不存在、密码错误等',
  },
  session_id: {
    type: DataTypes.STRING(128),
    allowNull: true,
    comment: '成功登录时的会话ID',
  },
  created_at: {
    type: DataTypes.DATE,
    defaultValue: DataTypes.NOW,
    comment: '创建时间',
  },
}, {
  tableName: 'login_logs',
  timestamps: false,
  comment: '登录日志表，用于安全审计',
  indexes: [
    { fields: ['user_id'], name: 'idx_user_id' },
    { fields: ['username'], name: 'idx_username' },
    { fields: ['ip_address'], name: 'idx_ip_address' },
    { fields: ['login_result'], name: 'idx_login_result' },
    { fields: ['created_at'], name: 'idx_created_at' },
  ],
});

// 定义关联关系
User.hasMany(UserSession, { foreignKey: 'user_id', as: 'sessions' });
UserSession.belongsTo(User, { foreignKey: 'user_id', as: 'user' });

User.hasMany(LoginLog, { foreignKey: 'user_id', as: 'loginLogs' });
LoginLog.belongsTo(User, { foreignKey: 'user_id', as: 'user' });

async function initDatabase() {
  try {
    console.log('🔗 连接到MySQL数据库...');

    // 测试连接
    await sequelize.authenticate();
    console.log('✅ 数据库连接成功');

    // 可选：先删除数据库内所有表，避免外键依赖导致的删除失败
    if (process.argv.includes('--drop-all')) {
      await dropAllTables(sequelize, DB_CONFIG.database);
    }

    console.log('🔄 同步数据库模型...');

    // 检查是否强制模式
    const forceMode = process.argv.includes('--force');

    // 同步所有模型到数据库
    await sequelize.sync({ force: forceMode }); // 根据参数决定是否删除现有表

    console.log('✅ 数据库表结构同步完成！');

    // 如果传入 --no-seed 则不创建测试数据（便于清库）
    if (!process.argv.includes('--no-seed')) {
      await createTestData();
    } else {
      console.log('⏭️  跳过种子数据创建 (--no-seed)');
    }

  } catch (error) {
    console.error('❌ 数据库初始化失败:', error);
    process.exit(1);
  } finally {
    await sequelize.close();
    console.log('🔒 数据库连接已关闭');
  }
}

async function dropAllTables(sequelize, dbName) {
  console.log('⚠️  正在删除数据库内所有表（包含外键依赖）...');
  try {
    await sequelize.query('SET FOREIGN_KEY_CHECKS = 0');
    const [rows] = await sequelize.query('SHOW TABLES');
    const key = `Tables_in_${dbName}`;
    const tables = rows.map(r => r[key]).filter(Boolean);
    if (tables.length === 0) {
      console.log('ℹ️  数据库中无表可删');
    } else {
      for (const table of tables) {
        console.log(`🗑️  DROP TABLE IF EXISTS \`${table}\``);
        await sequelize.query(`DROP TABLE IF EXISTS \`${table}\``);
      }
    }
  } catch (e) {
    console.error('❌ 删除所有表失败:', e);
    throw e;
  } finally {
    await sequelize.query('SET FOREIGN_KEY_CHECKS = 1');
  }
}

async function createTestData() {
  try {
    console.log('📝 检查测试数据...');

    // 检查是否已有测试用户
    const existingUser = await User.findOne({
      where: { username: 'testuser' }
    });
    const existingUser2 = await User.findOne({
      where: { username: 'testuser2' }
    });

    if (!existingUser) {
      const bcrypt = require('bcryptjs');
      const salt = 'randomsalt123';
      const passwordHash = await bcrypt.hash('password123' + salt, 10);

      // 创建测试用户
      const testUser = await User.create({
        username: 'testuser',
        password_hash: passwordHash,
        salt: salt,
        status: 1,
        login_count: 0,
      });

      console.log('✅ 测试用户创建成功:', {
        id: testUser.id,
        username: testUser.username
      });
    } else {
      console.log('ℹ️  测试用户已存在');
    }

    // 确保存在第二个测试用户
    if (!existingUser2) {
      const bcrypt = require('bcryptjs');
      const salt2 = 'randomsalt456';
      const passwordHash2 = await bcrypt.hash('password123' + salt2, 10);

      const testUser2 = await User.create({
        username: 'testuser2',
        password_hash: passwordHash2,
        salt: salt2,
        status: 1,
        login_count: 0,
      });

      console.log('✅ 第二个测试用户创建成功:', {
        id: testUser2.id,
        username: testUser2.username
      });
    } else {
      console.log('ℹ️  第二个测试用户已存在');
    }

    // 确保存在固定ID的用户（供单测使用：ID=1,2）
    // 如果当前最小ID大于1，则插入占位用户使得自增到达2
    const [minRow] = await sequelize.query('SELECT MIN(id) as minId, MAX(id) as maxId FROM users');
    const minId = minRow?.[0]?.minId;
    if (minId == null) {
      // 无用户，创建两个固定ID
      await sequelize.query("INSERT INTO users (id, username, password_hash, salt, status, login_count, created_at, updated_at) VALUES (1,'inviter','$2a$10$placeholder','salt',1,0,NOW(),NOW()), (2,'invitee','$2a$10$placeholder2','salt',1,0,NOW(),NOW())");
    } else {
      // 如果不存在ID=1，补齐
      const [exists1] = await sequelize.query('SELECT 1 FROM users WHERE id=1');
      if (!exists1 || exists1.length === 0) {
        await sequelize.query("INSERT INTO users (id, username, password_hash, salt, status, login_count, created_at, updated_at) VALUES (1,'inviter','$2a$10$placeholder','salt',1,0,NOW(),NOW())");
      }
      // 如果不存在ID=2，补齐
      const [exists2] = await sequelize.query('SELECT 1 FROM users WHERE id=2');
      if (!exists2 || exists2.length === 0) {
        await sequelize.query("INSERT INTO users (id, username, password_hash, salt, status, login_count, created_at, updated_at) VALUES (2,'invitee','$2a$10$placeholder2','salt',1,0,NOW(),NOW())");
      }
    }

  } catch (error) {
    console.error('⚠️  创建测试数据失败:', error);
  }
}

// 检查命令行参数
const force = process.argv.includes('--force');
if (force) {
  console.log('⚠️  强制模式：将删除所有现有表并重新创建');
}

// 执行初始化
if (require.main === module) {
  initDatabase();
}

module.exports = {
  sequelize,
  User,
  UserSession,
  LoginLog,
  initDatabase
};
